Complete Linux

home *** CD-ROM | disk | FTP | other *** search

/ Complete Linux / Complete Linux.iso / docs / apps / database / ingres04.lzh / doc / quel / quel.doc < prev next >

Wrap

Text File | 1992-11-19 | 26.1 KB | 600 lines

QUEL(QUEL) 2/23/79 QUEL(QUEL) NAME quel - QUEry Language for INGRES DESCRIPTION The following is a description of the general syntax of QUEL. Individual QUEL statements and commands are treated separately in the document; this section describes the syn- tactic classes from which the constituent parts of QUEL statements are drawn. 1. Comments A comment is an arbitrary sequence of characters bounded on the left by ``/*'' and on the right by ``*/'': /* This is a comment */ 2. Names Names in QUEL are sequences of no more than 12 alphanumeric characters, starting with an alphabetic. Underscore (_) is considered an alphabetic. All upper-case alphabetics ap- pearing anywhere except in strings are automatically and silently mapped into their lower-case counterparts. 3. Keywords The following identifiers are reserved for use as keywords and may not be used otherwise: abs all and any append ascii at atan avg avgu by concat copy cos count countu create define delete delim destroy exp float4 float8 from gamma help in index int1 int2 int4 integrity into is log max min mod modify not of on onto or permit print range replace retrieve save sin sqrt sum sumu to unique until unuse use view where 4. Constants There are three types of constants, corresponding to the three data types available in QUEL for data storage. 4.1. String constants Strings in QUEL are sequences of no more than 255 arbitrary ASCII characters bounded by double quotes ( " " ). Upper case alphabetics within strings are accepted literally. Also, in order to imbed quotes within strings, it is neces- sary to prefix them with `\' . The same convention applies to `\' itself. Only printing characters are allowed within strings. Non- printing characters (i.e. control characters) are converted to blanks. 4.2. Integer constants Integer constants in QUEL range from -2,147,483,647 to +2,147,483,647. Integer constants beyond that range will be converted to floating point. If the integer is greater than 32,767 or less than -32,767 then it will be left as a two byte integer. Otherwise it is converted to a four byte in- teger. 4.3. Floating point constants Floating constants consist of an integer part, a decimal point, and a fraction part or scientific notation of the following format: {<dig>} [.<dig>] [e|E [+|-] {<dig>}] Where <dig> is a digit, [] represents zero or one, {} represents zero or more, and | represents alternation. An exponent with a missing mantissa has a mantissa of 1 insert- ed. There may be no extra characters embedded in the string. Floating constants are taken to be double-precision quantities with a range of approximately -10**38 to +10**38 and a precision of 17 decimal digits. 5. Attributes An attribute is a construction of the form: variable.domain _V_a_r_i_a_b_l_e identifies a particular relation and can be thought of as standing for the rows or tuples of that relation. A variable is associated with a relation by means of a _r_a_n_g_e statement. _D_o_m_a_i_n is the name of one of the columns of the relation over which the variable ranges. Together they make up an attribute, which represents values of the named domain. If the attribute is a string type, it can be qualified with the substring notation. The substring notation is explained later. 6. Operators 6.1 Arithmetic operators Arithmetic operators take numeric type expressions as operands. Unary operators group right to left; binary operators group left to right. The operators (in order of descending precedence) are: +,- (unary) plus, minus ** exponentiation *,/ multiplication, division +,- (binary) addition, subtraction Parentheses may be used for arbitrary grouping. Arithmetic overflow and divide by zero are not checked on integer operations. Floating point operations are checked for over- flow, underflow, and divide by zero only if the appropriate machine hardware exists and has been enabled. 6.2 Arithmetic string operators The operator + is a string concatenator, like the HU func- tion _c_o_n_c_a_t; however, its syntax is cleaner and it is not limited to two arguments, but like its arithmetic counter- part, can be used without restriction. Its counterpart, -, is the string equivalent of the difference operator on sets, with the special property that only the first instance of the right hand side is deleted from the string. The binding properties of these two operators are exactly equivalent to the arithmetic plus and minus, which means that they can be used in conjunction with parentheses to form complex expres- sions. These two operators are most useful when used with the sub- string notation. 7. Expressions (a_expr) An expression is one of the following: constant attribute functional expression aggregate or aggregate function a combination of numeric expressions and arithmetic operators For the purposes of this document, an arbitrary expression will be refered to by the name _a__e_x_p_r. 8. Formats Every _a__e_x_p_r has a format denoted by a letter (_c, _i, or _f, for character, integer, or floating data types respectively) and a number indicating the number of bytes of storage occu- pied. Formats currently supported are listed below. The ranges of numeric types are indicated in parentheses. c1 - c255 character data of length 1-255 charac- ters i1 1-byte integer (-128 to +127) i2 2-byte integer (-32768 to +32767) i4 4-byte integer (-2,147,483,648 to +2,147,483,647) f4 4-byte floating (-10**38 to +10**38, 7 decimal digit precision) f8 8-byte floating (-10**38 to +10**38, 17 decimal digit precision) One numeric format can be converted to or substituted for any other numeric format. 9. Type Conversion. When operating on two numeric domains of different types, INGRES converts as necessary to make the types identical. When operating on an integer and a floating point number, the integer is converted to a floating point number before the operation. When operating on two integers of different sizes, the smaller is converted to the size of the larger. When operating on two floating point number of different size, the larger is converted to the smaller. The following table summarizes the possible combinations: i1 i2 i4 f4 f8 i1 - i1 i2 i4 f4 f8 i2 - i2 i2 i4 f4 f8 i4 - i4 i4 i4 f4 f8 f4 - f4 f4 f4 f4 f4 f8 - f8 f8 f8 f4 f8 INGRES provides five type conversion operators specifically for overriding the default actions. The operators are: int1(a_expr) result type i1 int2(a_expr) result type i2 int4(a_expr) result type i4 float4(a_expr) result type f4 float8(a_expr) result type f8 The type conversion operators convert their argument a_expr to the requested type. _A__e_x_p_r can be anything including character. If a character value cannot be converted, an er- ror occures and processing is halted. This can happen only if the syntax of the character value is incorrect. Overflow is not checked on conversion. 10. Target_list A target list is a parenthesized, comma separated list of one or more elements , each of which must be of one of the following forms: a) _r_e_s_u_l_t__a_t_t_n_a_m_e _i_s _a__e_x_p_r _R_e_s_u_l_t__a_t_t_n_a_m_e is the name of the attribute to be created (or an already existing attribute name in the case of update statements.) The equal sign (``='') may be used interchange- ably with _i_s. In the case where _a__e_x_p_r is anything other than a single attribute, this form must be used to assign a result name to the expression. b) _a_t_t_r_i_b_u_t_e In the case of a _r_e_t_r_i_e_v_e, the resultant domain will acquire the same name as that of the attribute being retrieved. In the case of update statements (_a_p_p_e_n_d, _r_e_p_l_a_c_e), the rela- tion being updated must have a domain with exactly that name. Inside the target list the keyword _a_l_l can be used to represent all domains. For example: range of e is employee retrieve (e.all) where e.salary > 10000 will retrieve all domains of employee for those tuples which satisfy the qualification. _A_l_l can be used in the target list of a _r_e_t_r_i_e_v_e or an _a_p_p_e_n_d. The domains will be in- serted in their ``create'' order, that is, the same order they were listed in the _c_r_e_a_t_e statement. 11. Comparison operators Comparison operators take arbitrary expressions as operands. < (less than) <= (less than or equal) > (greater than) >= (greater than or equal) = (equal to) != (not equal to) They are all of equal precedence. When comparisons are made on character attributes, all blanks are ignored. 12. Logical operators Logical operators take clauses as operands and group left- to-right: not (logical not; negation) and (logical and; conjunction) or (logical or; disjunction) _N_o_t has the highest precedence of the three. _A_n_d and _o_r have equal precedence. Parentheses may be used for arbi- trary grouping. 13. Qualification (qual) A _q_u_a_l_i_f_i_c_a_t_i_o_n consists of any number of clauses connected by logical operators. A clause is a pair of expressions connected by a comparison operator: a_expr comparison_operator a_expr Parentheses may be used for arbitrary grouping. A qualifi- cation may thus be: _c_l_a_u_s_e _n_o_t _q_u_a_l _q_u_a_l _o_r _q_u_a_l _q_u_a_l _a_n_d _q_u_a_l ( _q_u_a_l ) 14. Functional expressions A _f_u_n_c_t_i_o_n_a_l _e_x_p_r_e_s_s_i_o_n consists of a function name followed by a parenthesized (list of) operand(s). Functional expres- sions can be nested to any level. In the following list of functions supported (_n) represents an arbitrary numeric type expression. The format of the result is indicated on the right. abs(_n) - same as _n (absolute value) ascii(_n) - character string (converts numeric to character) atan(_n) - f8 (arctangent) concat(_a,_b) - character (character concatenation. See 16.2) cos(_n) - f8 (cosine) exp(_n) - f8 (exponential of _n) gamma(_n) - f8 (log gamma) log(_n) - f8 (natural logarithm) mod(_n,_b) - same as _b (_n modulo _b. _n and _b must be i1, i2, or i4) sin(_n) - f8 (sine) sqrt(_n) - f8 (square root) 15. Aggregate expressions Aggregate expressions provide a way to aggregate a computed expression over a set of tuples. 15.1. Aggregation operators The definitions of the aggregates are listed below. count - (i4) count of occurrences countu - (i4) count of unique occurrences sum - summation sumu - summation of unique values avg - (f8) average (sum/count) avgu - (f8) unique average (sumu/countu) max - maximum min - minimum any - (i2) value is 1 if any tuples satisfy the qualification, else it is 0 15.2. Simple aggregate _a_g_g_r_e_g_a_t_i_o_n__o_p_e_r_a_t_o_r (_a__e_x_p_r [ _w_h_e_r_e _q_u_a_l ] ) A simple aggregate evaluates to a single scalar value. _A__e_x_p_r is aggregated over the set of tuples satisfying the qualification (or all tuples in the range of the expression if no qualification is present). Operators _s_u_m and _a_v_g re- quire numeric type _a__e_x_p_r; _c_o_u_n_t, _a_n_y, _m_a_x and _m_i_n permit a character type attribute as well as numeric type _a__e_x_p_r. _S_i_m_p_l_e _a_g_g_r_e_g_a_t_e_s _a_r_e _c_o_m_p_l_e_t_e_l_y _l_o_c_a_l. That is, they are logically removed from the query, processed separately, and 15.3. ``_a_n_y'' aggregate It is sometimes useful to know if any tuples satisfy a par- ticular qualification. One way of doing this is by using the aggregate _c_o_u_n_t and checking whether the return is zero or non-zero. Using _a_n_y instead of _c_o_u_n_t is more efficient since processing is stopped, if possible, the first time a tuple satisfies a qualification. _A_n_y returns 1 if the qualification is true and 0 otherwise. 15.4. Aggregate functions _a_g_g_r_e_g_a_t_i_o_n__o_p_e_r_a_t_o_r (_a__e_x_p_r _b_y _b_y__d_o_m_a_i_n {, _b_y__d_o_m_a_i_n} [ _w_h_e_r_e _q_u_a_l ] ) Aggregate functions are extensions of simple aggregates. The _b_y operator groups (i.e. partitions) the set of qualify- ing tuples by _b_y__d_o_m_a_i_n values. For more than one _b_y__d_o_m_a_i_n, the values which are grouped by are the concate- nation of individual _b_y__d_o_m_a_i_n values. _A__e_x_p_r is as in sim- ple aggregates. The aggregate function evaluates to a set of aggregate results, one for each partition into which the set of qualifying tuples has been grouped. The aggregate value used during evaluation of the query is the value asso- ciated with the partition into which the tuple currently be- ing processed would fall. Unlike simple aggregates, aggregate functions are not com- pletely local. The _b_y__l_i_s_t, which differentiates aggregate functions from simple aggregates, is global to the query. Domains in the _b_y__l_i_s_t are automatically linked to the other domains in the query which are in the same relation. Example: /* retrieve the average salary for the employees working for each manager */ range of e is employee retrieve (e.manager, avesal=avg(e.salary by e.manager)) 15.5 Aggregates on Unique Values. It is occasionally necessary to aggregate on unique values of an expression. The _a_v_g_u, _s_u_m_u, and _c_o_u_n_t_u aggregates all remove duplicate values before performing the aggregation. For example: count(e.manager) would tell you how many occurrences of _e._m_a_n_a_g_e_r exist. But countu(e.manager) would tell you how many unique values of _e._m_a_n_a_g_e_r exist. 16. Special character operators There are four special features which are particular to character domains. 16.1 Pattern matching characters There are eleven characters which take on special meaning when used in character constants (strings): * matches any string of zero or more characters. ? matches any single character. [..] matches any of characters in the brackets. ##1 matches any string of zero or more characters. ##2 matches any string of zero or more characters. ##3 matches any string of zero or more characters. ##4 matches any string of zero or more characters. ##5 matches any string of zero or more characters. ##6 matches any string of zero or more characters. ##7 matches any string of zero or more characters. ##8 matches any string of zero or more characters. ##9 matches any string of zero or more characters. ##0 matches all instances of strings between two occu- rances of ##0. These characters can be used in any combination to form a variety of tests. For example: where e.name = "##1Kalash##2Joe##4" - matches any occu- rance of "Kalash", followed by "Joe". where e.name = "##0Ingres##0" - matches all occurances of "Ingres" within a line. where e.name = "*" - matches any name. where e.name = "E*" - matches any name starting with "E". where e.name = "*ein" - matches all names ending with "ein" where e.name = "*[aeiou]*" - matches any name with at least one vowel. where e.name = "Allman?" - matches any seven character name starting with "Allman". where e.name = "[A-J]*" - matches any name starting with A,B,..,J. The special meaning of the pattern matching characters can be disabled by preceding them with a `\'. Thus ``\*'' refers to the character ``*''. When the special characters appear in the target list they must be escaped. For exam- ple: title = "\*\*\* ingres \*\*\*" is the correct way to assign the string ``*** ingres ***'' to the domain ``title''. 16.1.1 Numbered Wildcards The numbered wildcards are unique in that they may also ap- pear in a target list, as well as in a qualification. Each unique numbered wildcard used retains the same value in both the target list and the qualification list. Thus a query such as replace t(text = "##1the##2") where t.text = "##1THE##2" will replace an occurence of "THE" in t.text with "the". The special global wildcard ##0 when used in the query replace t(text = "##0the##0") where t.text = "##0THE##0" will replace all occurrences of "THE" with "the". 16.2 Concatenation There is a concatenation operator which can form one charac- ter string from two. Its syntax is ``concat(field1, field2)''. The size of the new character string is the sum of the sizes of the original two. Trailing blanks are trimmed from the first field, the second field is con- catenated and the remainder is blank padded. The result is never trimmed to 0 length, however. Concat can be arbi- trarily nested inside other concats. For example: name = concat(concat(x.lastname, ","), x.firstname) will concatenate x.lastname with a comma and then concaten- ate x.firstname to that. 16.3 Ascii (numeric to character translation) The _a_s_c_i_i function can be used to convert a numeric field to its character representation. This can be useful when it is desired to compare a numeric value with a character value. For example: retrieve ( ... ) where x.chardomain = ascii(x.numdomain) _A_s_c_i_i can be applied to a character value. The result is simply the character value unchanged. The numeric conver- sion formats are determined by the printing formats (see ingres(unix)). 16.4 Substring notation Any string attribute can be broken up into into a smaller substring using the following substring operators. variable.domain(X,Y) variable.domain(X,Y% variable.domain%X,Y) variable.domain%X,Y% Each of the above represents a certain substring of _d_o_m_a_i_n, denoted by the endpoints X and Y. Whether the endpoints are to be included or not is determined by the parentheses (ex- clusion) and percent signs (inclusion). _X and _Y (optional) consist of a required part with optional qualifiers. The required part can be any of the following: a string w (a word) c (a character) A user defined delimiter (see delim(quel)) The optional qualifiers are a preceding digit, _i, which specifies to look for the _i_t_h occurence, and a trailing $, which specifies to search backwards from the end of the string. The rules for searching are very simple. Without the $, the value of _X chosen is the _i_t_h occurrance (the default value of _i is one) from the left end of the string. The search for _Y, if it is requested, starts after the end of _X. A dollar sign, however, always specifies that the search start from the end of the string regardless of the value of _X. For illustrative purposes, assume a text field to contain the following: I saw the dog, the cat, and the duck take a walk. Then the following constructs would have the attached values: r.text(3w,2"the"% dog, the cat, and the r.text%2"the"$,$% the cat, and the duck take a walk. When combined with the arithemtic string operators this fa- cility can the quite powerful. For example, to remove the dog from the sentence requires only the simple following query: replace r(text = r.text - r.text%"the","the")) Or perhaps only the baby duck was taking a walk: replace r(text = r.text%"I","the"% + "baby" + r.text%"duck",$)) SEE ALSO append(quel), delete(quel), delim(quel), range(quel), replace(quel), retrieve(quel), ingres(unix) BUGS The maximum number of variables which can appear in one query is 10. Numeric overflow, underflow, and divide by zero are not detected. When converting between numeric types, overflow is not checked.